package zy.dao.buy.prepay.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.buy.prepay.BuyPrepayDAO;
import zy.entity.buy.prepay.T_Buy_Prepay;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;

@Repository
public class BuyPrepayDAOImpl extends BaseDaoImpl implements BuyPrepayDAO{
	@Override
	public Integer count(Map<String, Object> params) {
		Object pp_type = params.get("pp_type");
		Object pp_ar_state = params.get("pp_ar_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object pp_supply_code = params.get("pp_supply_code");
		Object pp_ba_code = params.get("pp_ba_code");
		Object pp_manager = params.get("pp_manager");
		Object pp_number = params.get("pp_number");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_buy_prepay t");
		sql.append(" JOIN t_buy_supply sp ON sp_code = pp_supply_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if (StringUtil.isNotEmpty(pp_type)) {
			sql.append(" AND pp_type = :pp_type ");
		}
		if (StringUtil.isNotEmpty(pp_ar_state)) {
			sql.append(" AND pp_ar_state = :pp_ar_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND pp_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND pp_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(pp_supply_code)) {
			sql.append(" AND pp_supply_code = :pp_supply_code ");
		}
		if (StringUtil.isNotEmpty(pp_ba_code)) {
			sql.append(" AND pp_ba_code = :pp_ba_code ");
		}
		if (StringUtil.isNotEmpty(pp_manager)) {
			sql.append(" AND pp_manager = :pp_manager ");
		}
		if (StringUtil.isNotEmpty(pp_number)) {
			sql.append(" AND INSTR(pp_number,:pp_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Buy_Prepay> list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object pp_type = params.get("pp_type");
		Object pp_ar_state = params.get("pp_ar_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object pp_supply_code = params.get("pp_supply_code");
		Object pp_ba_code = params.get("pp_ba_code");
		Object pp_manager = params.get("pp_manager");
		Object pp_number = params.get("pp_number");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT pp_id,pp_number,pp_date,pp_supply_code,pp_type,pp_money,pp_maker,pp_manager,pp_ba_code,pp_remark,pp_ar_date,pp_ar_state,pp_sysdate,");
		sql.append(" pp_us_id,t.companyid,sp_name AS supply_name,");
		sql.append(" (SELECT ba_name FROM t_money_bank ba WHERE ba_code = pp_ba_code AND ba.companyid = t.companyid LIMIT 1) AS ba_name");
		sql.append(" FROM t_buy_prepay t");
		sql.append(" JOIN t_buy_supply sp ON sp_code = pp_supply_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if (StringUtil.isNotEmpty(pp_type)) {
			sql.append(" AND pp_type = :pp_type ");
		}
		if (StringUtil.isNotEmpty(pp_ar_state)) {
			sql.append(" AND pp_ar_state = :pp_ar_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND pp_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND pp_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(pp_supply_code)) {
			sql.append(" AND pp_supply_code = :pp_supply_code ");
		}
		if (StringUtil.isNotEmpty(pp_ba_code)) {
			sql.append(" AND pp_ba_code = :pp_ba_code ");
		}
		if (StringUtil.isNotEmpty(pp_manager)) {
			sql.append(" AND pp_manager = :pp_manager ");
		}
		if (StringUtil.isNotEmpty(pp_number)) {
			sql.append(" AND INSTR(pp_number,:pp_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY pp_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Buy_Prepay.class));
	}

	@Override
	public T_Buy_Prepay load(Integer pp_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT pp_id,pp_number,pp_date,pp_supply_code,pp_type,pp_money,pp_maker,pp_manager,pp_ba_code,pp_remark,pp_ar_date,pp_ar_state,pp_sysdate,");
		sql.append(" pp_us_id,t.companyid,sp_name AS supply_name,");
		sql.append(" (SELECT ba_name FROM t_money_bank ba WHERE ba_code = pp_ba_code AND ba.companyid = t.companyid LIMIT 1) AS ba_name");
		sql.append(" FROM t_buy_prepay t");
		sql.append(" JOIN t_buy_supply sp ON sp_code = pp_supply_code AND sp.companyid = t.companyid");
		sql.append(" WHERE pp_id = :pp_id");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("pp_id", pp_id),
					new BeanPropertyRowMapper<>(T_Buy_Prepay.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Buy_Prepay load(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT pp_id,pp_number,pp_date,pp_supply_code,pp_type,pp_money,pp_maker,pp_manager,pp_ba_code,pp_remark,pp_ar_date,pp_ar_state,pp_sysdate,");
		sql.append(" pp_us_id,t.companyid,sp_name AS supply_name,");
		sql.append(" (SELECT ba_name FROM t_money_bank ba WHERE ba_code = pp_ba_code AND ba.companyid = t.companyid LIMIT 1) AS ba_name");
		sql.append(" FROM t_buy_prepay t");
		sql.append(" JOIN t_buy_supply sp ON sp_code = pp_supply_code AND sp.companyid = t.companyid");
		sql.append(" WHERE pp_number = :pp_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("pp_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Buy_Prepay.class));
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public T_Buy_Prepay check(String number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT pp_id,pp_number,pp_date,pp_supply_code,pp_type,pp_money,pp_manager,pp_ba_code,pp_remark,pp_ar_state,pp_us_id,t.companyid");
		sql.append(" FROM t_buy_prepay t");
		sql.append(" WHERE pp_number = :pp_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("pp_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Buy_Prepay.class));
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public void save(T_Buy_Prepay prepay) {
		String prefix = CommonUtil.NUMBER_PREFIX_BUY_PREPAY + DateUtil.getYearMonthDateYYYYMMDD();
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT CONCAT(:prefix,f_addnumber(MAX(pp_number))) AS new_number");
		sql.append(" FROM t_buy_prepay");
		sql.append(" WHERE 1=1");
		sql.append(" AND INSTR(pp_number,:prefix) > 0");
		sql.append(" AND companyid = :companyid");
		String new_number = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("prefix", prefix).addValue("companyid", prepay.getCompanyid()), String.class);
		prepay.setPp_number(new_number);
		sql.setLength(0);
		sql.append(" INSERT INTO t_buy_prepay");
		sql.append(" (pp_number,pp_date,pp_supply_code,pp_type,pp_money,pp_maker,pp_manager,pp_ba_code,pp_remark,pp_ar_date,pp_ar_state,pp_sysdate,pp_us_id,companyid)");
		sql.append(" VALUES");
		sql.append(" (:pp_number,:pp_date,:pp_supply_code,:pp_type,:pp_money,:pp_maker,:pp_manager,:pp_ba_code,:pp_remark,:pp_ar_date,:pp_ar_state,:pp_sysdate,:pp_us_id,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(prepay),holder);
		prepay.setPp_id(holder.getKey().intValue());
		
	}

	@Override
	public void update(T_Buy_Prepay prepay) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_buy_prepay");
		sql.append(" SET pp_date=:pp_date");
		sql.append(" ,pp_supply_code=:pp_supply_code");
		sql.append(" ,pp_type=:pp_type");
		sql.append(" ,pp_money=:pp_money");
		sql.append(" ,pp_maker=:pp_maker");
		sql.append(" ,pp_manager=:pp_manager");
		sql.append(" ,pp_ba_code=:pp_ba_code");
		sql.append(" ,pp_remark=:pp_remark");
		sql.append(" ,pp_ar_date=:pp_ar_date");
		sql.append(" ,pp_ar_state=:pp_ar_state");
		sql.append(" ,pp_us_id=:pp_us_id");
		sql.append(" WHERE pp_id=:pp_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(prepay));
	}

	@Override
	public void updateApprove(T_Buy_Prepay prepay) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_buy_prepay");
		sql.append(" SET pp_ar_state=:pp_ar_state");
		sql.append(" ,pp_ar_date = :pp_ar_date");
		sql.append(" WHERE pp_id=:pp_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(prepay));
	}

	@Override
	public void del(String pp_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_buy_prepay");
		sql.append(" WHERE pp_number=:pp_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("pp_number", pp_number).addValue("companyid", companyid));
	}
}
